﻿DECLARE @RC int
DECLARE @VersionNumber varchar(25)
DECLARE @SetAsCurrent bit
DECLARE @RolloutDate date

-- TODO: Set parameter values here.
SET @VersionNumber = '0.0.1.10'
SET @SetAsCurrent = 1
SET @RolloutDate = GETDATE()

EXECUTE @RC = [core].[AddVersion] 
   @VersionNumber
  ,@SetAsCurrent
  ,@RolloutDate
GO

IF OBJECT_ID('[finance].[fn_ExistsBankkontoByKontonummerAndBankleitzahl]') IS NOT NULL
BEGIN
	DROP FUNCTION [finance].[fn_ExistsBankkontoByKontonummerAndBankleitzahl]
END
GO

CREATE FUNCTION [finance].[fn_ExistsBankkontoByKontonummerAndBankleitzahl]
(
	@Kontonummer NVARCHAR(50),
	@Bankleitzahl NVARCHAR(50)
)
RETURNS BIT
AS
BEGIN
	DECLARE @returnValue BIT
	SELECT @returnValue = COUNT(1) FROM [finance].[Bankkonto]
		WHERE	Kontonummer = @Kontonummer
		AND		Bankleitzahl = @Bankleitzahl
	RETURN @returnValue
END
GO

ALTER PROCEDURE [finance].[AddBankkonto]
	@Kontonummer		NVARCHAR(50),
	@Bankleitzahl		NVARCHAR(50),
	@Bankname			NVARCHAR(256),
	@Kontoname			NVARCHAR(256),
	@IBAN				NVARCHAR(50) = NULL,
	@CreatedBy			NVARCHAR(256),
	@CreatedFromSource	NVARCHAR(256),
	@VersionNumber		VARCHAR(25),
	@SessionID			VARCHAR(50),
	@LanguageID			INT = NULL
AS
BEGIN
	DECLARE
		@CreatedAt DATETIME = GETDATE(),
		@errorReturn INT = 0,
		@errorText NVARCHAR(1024)

	IF @LanguageID IS NULL
	BEGIN
		SET @LanguageID = [core].[fn_GetDefaultLanguageID]();
	END

	-- Kontonummer prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @Kontonummer, 0, 0, 0, 50, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10001, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10001, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10001;
	END

	-- Bankleitzahl prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @Bankleitzahl, 0, 0, 0, 50, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10002, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10002, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10002;
	END

	IF finance.fn_ExistsBankkontoByKontonummerAndBankleitzahl(@Kontonummer, @Bankleitzahl) = 1
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10006, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10006, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10006;
	END

	-- Bankname prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @Bankname, 0, 0, 0, 256, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10003, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10003, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10003;
	END

	-- Kontoname prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @Bankname, 0, 0, 0, 256, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10004, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10004, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10004;
	END
	
	IF [finance].[fn_ExistsBankkontoByKontoname](@Kontoname) = 1
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10000, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10000, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10000;
	END

	-- IBAN prüfen
	EXEC @errorReturn = [core].[ValidateStringParameter] @IBAN, 1, 1, 0, 50, 'finance', 'Bankkonto', 1, @VersionNumber, @SessionID, @LanguageID;

	IF @errorReturn <> 0
	BEGIN
		EXEC [core].[SaveLogging] @CreatedAt, 3, @VersionNumber, @CreatedBy, @CreatedFromSource, 10005, @LanguageID, @SessionID;
		SELECT @errorText = core.fn_GetErrorTextByErrorCode(10005, @LanguageID);
		RAISERROR(@errorText, 16, 1);
		RETURN 10005;
	END
	
	INSERT INTO [finance].[Bankkonto]
	(Kontonummer, Bankleitzahl, Bankname, Kontoname, IBAN, CreatedAt, CreatedBy, CreatedFromSource)
	SELECT @Kontonummer, @Bankleitzahl, @Bankname, @Kontoname, @IBAN, @CreatedAt, @CreatedBy, @CreatedFromSource

	RETURN 0;
END  